(ns acst.models.schema
  (:require [acst.models.db :refer [db do-commands]]
            [acst.models.roles :refer [roles]]
            [clojure.java.jdbc :as sql]))


(def tables [

             :roles                                       ;;用户角色表,权限限制
             [[:role_id "serial PRIMARY KEY"]
              [:name "text NOT NULL UNIQUE"]
              [:permission "int NOT NULL UNIQUE"]]                    ;;采用二进制表示权限

             :users                                       ;;用户表
             [[:user_id "serial PRIMARY KEY"]
              [:username "text NOT NULL UNIQUE"]
              [:password "text NOT NULL"]
              [:number "text UNIQUE"]
              [:mobile "text UNIQUE"]
              [:permission "int NOT NULL REFERENCES roles(permission)"]]

             :news                                        ;;新闻表
             [[:news_id "bigserial PRIMARY KEY"]
              [:create_date "timestamp NOT NULL default now()"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id) ON DELETE NO ACTION"]
              [:title "text NOT NULL"]
              [:content "text NOT NULL"]]

             :news_comments                                   ;;新闻评论表
             [[:comment_id "bigserial PRIMARY KEY"]
              [:news_id "int NOT NULL REFERENCES news(news_id) ON DELETE CASCADE"]
              [:reply_root_id "int"]
              [:reply_id "int REFERENCES news_comments(comment_id)"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id) ON DELETE CASCADE"]
              [:create_date "timestamp NOT NULL default now()"]
              [:content "text NOT NULL"]
              [:status "int NOT NULL"]]

             :activities
             [[:activity_id "bigserial PRIMARY KEY"]
              [:create_date "timestamp NOT NULL default now()"]
              [:deadline "timestamp NOT NULL default now() + interval '15 days' CHECK(deadline > create_date)"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id) ON DELETE NO ACTION"]
              [:title "text NOT NULL"]
              [:content "text NOT NULL"]]

             :blogs                                         ;;博客表
             [[:blog_id "bigserial PRIMARY KEY"]
              [:create_date "timestamp NOT NULL default now()"]
              [:owner_id "int NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE CASCADE"]]

             :articles
             [[:article_id "bigserial PRIMARY KEY"]
              [:blog_id "int NOT NULL REFERENCES blogs(blog_id) ON DELETE CASCADE"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id) ON DELETE CASCADE"]
              [:create_date "timestamp NOT NULL default now()"]
              [:title "text NOT NULL"]
              [:content "text NOT NULL"]]

             :article_comments                                   ;;文章评论表
             [[:comment_id "bigserial PRIMARY KEY"]
              [:article_id "int NOT NULL REFERENCES articles(article_id) ON DELETE CASCADE"]
              [:create_date "timestamp NOT NULL default now()"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id) ON DELETE CASCADE"]
              [:content "text NOT NULL"]
              [:reply_root_id "int"]
              [:reply_id "int REFERENCES article_comments(comment_id)"]]

             :memos
             [[:memo_id "bigserial PRIMARY KEY"]
              [:create_date "timestamp NOT NULL default now()"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id)"]
              [:content "text NOT NULL"]
              [:pic_url "text"]
              [:status "int NOT NULL"]]                   ;;0 待审核 1 未通过审核 2 已删除 3 通过审核 4

             :memo_comments
             [[:comment_id "bigserial PRIMARY KEY"]
              [:memo_id "int NOT NULL REFERENCES memos(memo_id) ON DELETE CASCADE"]
              [:reply_root_id "int"]
              [:reply_id "int REFERENCES memo_comments(comment_id)"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id) ON DELETE CASCADE"]
              [:create_date "timestamp NOT NULL default now()"]
              [:content "text NOT NULL"]
              [:status "int NOT NULL"]]                     ;;0 已删除 1 未删除

             :forms_constraint
             [[:constraint_id "bigserial PRIMARY KEY"]
              [:create_date "timestamp NOT NULL default now()"]
              [:activity_id "int NOT NULL REFERENCES activities(activity_id) ON DELETE CASCADE UNIQUE"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id) ON DELETE NO ACTION"]
              [:form_constraint "jsonb NOT NULL"]]

             :entry_forms
             [[:form_id "bigserial PRIMARY KEY"]
              [:publisher_id "int NOT NULL REFERENCES users(user_id) ON DELETE CASCADE"]
              [:create_date "timestamp NOT NULL default now()"]
              [:activity_id "int NOT NULL REFERENCES activities(activity_id) ON DELETE CASCADE"]
              [:value "jsonb NOT NULL"]]
             ])


(defn create-tables! []
  (doseq [kv (partition 2 tables)]
    (do-commands sql/create-table-ddl (first kv) (second kv))))

(defn insert-roles! []
  (doseq [role roles]
         (sql/insert! db :roles role)))

(defn create-db! []
  (create-tables!)
  (insert-roles!))

(def test-data! []
  )


